import platform = require('../../../../../platform/common/baseCommon');
import dbconnector = require('../../../../../platform/server/dataService');
import db = require('../../../../../platform/server/data');
import appServer = require('../../../../../platform/server/appServer');

let tzrjbtzAnalysysDataService = new dbconnector.dataTableService();

//公司数据服务地址
tzrjbtzAnalysysDataService.serviceUrl = 'http://192.168.5.39:18080/ATSDT_SRV/HS/JsonRpc/SQLForNodeService';

let connectInfo = new dbconnector.connectionInfo('test', 'jdbc:oracle:thin:@192.168.5.30/ats60', 'ats50',
    'O6KMLUAIyuKp', 'oracle.jdbc.driver.OracleDriver');

tzrjbtzAnalysysDataService.dbConnectInfo = connectInfo;

tzrjbtzAnalysysDataService.sqlGenerator = new dbconnector.baseDmlStatementGenerator();

/**
 *    佛山市投资人基本特征分析
 */

/**
 * 投资人年龄分布
 */
let cmd_fsgs_tzr_nl = new db.dataCommandInfo();
cmd_fsgs_tzr_nl.id = 'cmd_fsgs_tzr_nl';
cmd_fsgs_tzr_nl.name = '投资人年龄分布';
cmd_fsgs_tzr_nl.sql = `select * from KF_FSGS_TZRJBTZFX t where t.type='3' `;
/**
 * 投资人投资行业类型分布
 */
let cmd_fsgs_tzr_tzhylx = new db.dataCommandInfo();
cmd_fsgs_tzr_tzhylx.id = 'cmd_fsgs_tzr_tzhylx';
cmd_fsgs_tzr_tzhylx.name = '投资人投资行业类型分布';
cmd_fsgs_tzr_tzhylx.sql = `select * from KF_FSGS_TZRJBTZFX t where t.type= 2 order by num desc `;


/**
 * 投资人来源省份分布
 */
let cmd_fsgs_tzr_lysf = new db.dataCommandInfo();
cmd_fsgs_tzr_lysf.id = 'cmd_fsgs_tzr_lysf';
cmd_fsgs_tzr_lysf.name = '投资人来源省份分布';
cmd_fsgs_tzr_lysf.sql = `select NAME, NUM
                            from (select RANK() OVER(ORDER BY num desc) AS PM, name, num
                                    FROM kf_fsgs_tzrjbtzfx t
                                where type = 1)
                            where PM <= 10
                            UNION ALL
                            select '其它' NAME, SUM(NUM) NUM
                            from (select RANK() OVER(ORDER BY num desc) AS PM, name, num
                                    FROM kf_fsgs_tzrjbtzfx t
                                where type = 1)
                            where PM > 10 `;

/**
 * 投资人姓氏占比
 */
let cmd_fsgs_tzr_xszb = new db.dataCommandInfo();
cmd_fsgs_tzr_xszb.id = 'cmd_fsgs_tzr_xszb';
cmd_fsgs_tzr_xszb.name = '投资人姓氏占比';
cmd_fsgs_tzr_xszb.sql = `select NAME, NUM
                        from (select RANK() OVER(ORDER BY num desc) AS PM, name, num
                                FROM ats50.kf_fsgs_tzrjbtzfx t
                            where type = 4)
                        where PM <= 20
                        UNION ALL
                        select '其它' NAME, SUM(NUM) NUM
                        from (select RANK() OVER(ORDER BY num desc) AS PM, name, num
                                FROM ats50.kf_fsgs_tzrjbtzfx t
                            where type = 4)
                        where PM > 20`;

/**
 * 投资人年龄分布,放大，同“投资人年龄分布”的命令id
 */

/**
 * 投资人所属地
 */
let cmd_fsgs_tzr_ssd = new db.dataCommandInfo();
cmd_fsgs_tzr_ssd.id = 'cmd_fsgs_tzr_ssd';
cmd_fsgs_tzr_ssd.name = '投资人所属地';
cmd_fsgs_tzr_ssd.sql = `SELECT NAME, NUM
                        FROM (SELECT RANK() OVER(ORDER BY SUM(NUM) DESC) AS PM,
                                    D.NAME,
                                    SUM(NUM) NUM
                                FROM ATS50.KF_FSGS_TZRZHXX T, ATS50.KF_FSGS_SFZHMQYB D
                            
                            WHERE T.SQY = D.DM
                                AND {0}
                            GROUP BY D.NAME
                            ORDER BY NUM DESC)
                        WHERE PM <= 50

                        UNION ALL
                        SELECT '其它' NAME, SUM(NUM)
                        FROM (SELECT RANK() OVER(ORDER BY SUM(NUM) DESC) AS PM,
                                    D.NAME,
                                    SUM(NUM) NUM
                                FROM ATS50.KF_FSGS_TZRZHXX T, ATS50.KF_FSGS_SFZHMQYB D
                            
                            WHERE T.SQY = D.DM
                                AND {0}
                            GROUP BY D.NAME
                            ORDER BY NUM DESC)
                        WHERE PM > 50
                        `;
/** 数据过滤器 */
let filter_fsgs_tzr_ssd = new db.baseDataFilter()
    .and(
    new db.sqlFilter("sf", "T.sf>=?", "省份"),

);
cmd_fsgs_tzr_ssd.dataFilter = [filter_fsgs_tzr_ssd];
/**
 * 投资人所属省份
 */
let cmd_fsgs_tzr_sssf = new db.dataCommandInfo();
cmd_fsgs_tzr_sssf.id = 'ccmd_fsgs_tzr_sssf';
cmd_fsgs_tzr_sssf.name = '投资人所属省份';
cmd_fsgs_tzr_sssf.sql = `SELECT T.SF SF, T.NAME NAME, SUM(NUM) NUM
                        FROM ATS50.KF_FSGS_TZRZHXX T
                        WHERE {0}
                        GROUP BY T.SF, T.NAME
                        ORDER BY NUM DESC
                        `;
/** 数据过滤器 */
let filter_fsgs_tzr_sssf = new db.baseDataFilter()
    .and(
    new db.sqlFilter("name", "T.name=?", "姓"),

);
cmd_fsgs_tzr_ssd.dataFilter = [filter_fsgs_tzr_sssf];

/**
 * 按年龄段分析投资人变化趋势
 */
let cmd_fsgs_tzr_anldfxtzr = new db.dataCommandInfo();
cmd_fsgs_tzr_anldfxtzr.id = 'cmd_fsgs_tzr_anldfxtzr';
cmd_fsgs_tzr_anldfxtzr.name = '投资人年龄分布';
cmd_fsgs_tzr_anldfxtzr.sql = `SELECT SUM(NUM), TO_DATE(NRF, 'yyyy-mm') NRF
                            FROM ats50.KF_FSGS_TZRNLFX T
                            WHERE {0}
                            AND NRF >= '2015-01'
                            GROUP BY NRF
                            ORDER BY NRF
                            `;
/** 数据过滤器 */
let filter_fsgs_tzr_anldfxtzr = new db.baseDataFilter()
    .and(
    new db.sqlFilter("type", "T.type=?", "年龄"),

);
cmd_fsgs_tzr_anldfxtzr.dataFilter = [filter_fsgs_tzr_anldfxtzr];

tzrjbtzAnalysysDataService.commandList = [cmd_fsgs_tzr_nl, cmd_fsgs_tzr_lysf, cmd_fsgs_tzr_xszb,
    cmd_fsgs_tzr_tzhylx, cmd_fsgs_tzr_ssd, cmd_fsgs_tzr_anldfxtzr, cmd_fsgs_tzr_sssf];

//加到服务列表
platform.fetchAddonProtoType(tzrjbtzAnalysysDataService);
export let dataServiceInfo = new appServer.serviceInfo();
dataServiceInfo.serviceName = "tzrjbtzAnalysysDataService";
dataServiceInfo.serviceObject = tzrjbtzAnalysysDataService;







